package org.alex.samples.util;

import java.util.HashMap;
import java.util.Map;

public class Bysql {

	public static void main(String[] args) {
		// 清除表数据
		System.out.println("TRUNCATE exam_role;");
		System.out.println("TRUNCATE exam_subject;");
		System.out.println("TRUNCATE exam_test;");
		System.out.println("TRUNCATE exam_test_type;");
		System.out.println("TRUNCATE exam_user;");
		System.out.println("TRUNCATE exam_user_role;");
		System.out.println("TRUNCATE exam_user_subject;");

		// exam_user 用户表
		System.out.println("INSERT INTO exam_user(user_id,user_name,password,tel,email,address,birthday,gender,"
				+ "last_lock_time,login_fail_count) VALUE('jiaoshi','教师','" + MD5Util.toMD5("123456")
				+ "','13800138000','123456@126.com','中山职业技术学院','2018-01-20',1,'2016-01-01 00:00:00',0" + ");");
		System.out.println("INSERT INTO exam_user(user_id,user_name,password,tel,email,address,birthday,gender,"
				+ "last_lock_time,login_fail_count) VALUE('guangliyuan','管理员','" + MD5Util.toMD5("123456")
				+ "','13800138000','123456@126.com','中山职业技术学院','2018-01-20',0,'2016-01-01 00:00:00',0" + ");");
		for (int i = 3; i <= 103; i++) {
			int k = i % 2 != 0 ? 0 : 1;
			String sql = "INSERT INTO exam_user(user_id,user_name,password,tel,email,address,birthday,gender,"
					+ "last_lock_time,login_fail_count) VALUE('student" + i + "','学生" + i + "号','"
					+ MD5Util.toMD5("123456") + "','13800138000','123456@126.com','中山职业技术学院','2018-01-20'," + k
					+ ",'2016-01-01 00:00:00',0" + ");";
			System.out.println(sql);
		}

		// exam_subject 科目表
		for (int i = 1; i <= 6; i++) {
			int test_count = (int) ((Math.random() + 0.8) * 25); // 20-45的随机数
			int status = i % 2 != 0 ? 0 : 1;
			String sql = "INSERT INTO exam_subject(name,description,test_count,total_time,total_score,status) VALUE("
					+ "'测试科目" + i + "','测试科目" + i + "'," + test_count + ",90,100," + status + ");";
			System.out.println(sql);
		}

		// exam_user_subject 用户与科目表
		for (int k = 1; k <= 6; k++) {
			Map<String, String> userId = new HashMap<String, String>();
			for (int i = 1; i <= 30; i++) {
				// int sub_id = (int) ((Math.random() + 0.3) * 5); //1-6的随机数
				String user_id ="student"+(int)((Math.random() * 100) + 3)+""; // 3-103的随机数
				if (userId.containsKey(user_id)) {
					continue;
				}
				userId.put(user_id, user_id);
				String sql = "INSERT INTO exam_user_subject(user_id,sub_id) VALUE('" + user_id + "'," + k + ");";
				System.out.println(sql);
			}
		}

		// exam_role
		System.out.println("INSERT INTO exam_role(name) VALUE('学生');");
		System.out.println("INSERT INTO exam_role(name) VALUE('教师');");
		System.out.println("INSERT INTO exam_role(name) VALUE('管理员');");

		// exam_test 试题表
		for (int i = 1; i <= 270; i++) {
			int sub_id = (int) ((Math.random() + 0.3) * 5); // 1-6的随机数
			int test_type_id = (int) (Math.random() + 1.5); // 1-2的随机数
			String answer = test_type_id == 1 ? "A" : "B 、D";
			String sql = "INSERT INTO exam_test(content,choose_a,choose_b,choose_c,choose_d,sub_id,test_type_id,answer) "
					+ " VALUE('测试题目" + i + "','选项A','选项B','选项C','选项D'," + sub_id + "," + test_type_id + ",'" + answer
					+ "');";
			System.out.println(sql);
		}

		// exam_test_type 试题类型表
		System.out.println("INSERT INTO exam_test_type(test_type,test_type_score) VALUE('单选题',1);");
		System.out.println("INSERT INTO exam_test_type(test_type,test_type_score) VALUE('多选题',2);");

		// exam_user_role 用户权限表
		System.out.println("INSERT INTO exam_user_role(role_id,user_id) VALUE(2,'guanliyuan');");
		System.out.println("INSERT INTO exam_user_role(role_id,user_id) VALUE(3,'jiaoshi');");
		for (int i = 3; i <= 103; i++) {
			System.out.println("INSERT INTO exam_user_role(role_id,user_id) VALUE(1,'student" + i + "');");
		}
	}

}
